﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spRestoreIsAllowed]
#-- Purpose:		Determines if the user logged in has permissions to restore
#--					the database (via ORIGINAL_USER)
#--	Last Update:	04/25/2012
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spRestoreIsAllowed]
(	
	@database_name		varchar(255),
	@is_allowed			bit				OUTPUT
)
AS 

SET NOCOUNT ON

--- If the database is not online
DECLARE @is_restoring bit
EXEC	@is_restoring = dbo.spIsDatabaseRestoring @database_name

IF @is_restoring = 1
  BEGIN
	SELECT	@is_allowed = 1
  END
ELSE
  BEGIN
	--- Declare Local Variables
	DECLARE @db_owners TABLE ( name sysname )
	DECLARE @win_users TABLE ( account_name sysname, type char(8), privilege char(9), mapped_login_name sysname, permission_path sysname NULL )
	DECLARE	@has_rights bit, @reason sysname, @sSQL varchar(MAX), @group_name sysname, @user_name sysname
	SELECT	@has_rights = 0, @reason = 'No rights', @user_name = ORIGINAL_LOGIN(),
			@sSQL = 'USE ' + QUOTENAME(@database_name) + '; 
					SELECT	p.name
					FROM	sys.database_principals p
					JOIN	sys.database_role_members rm ON p.principal_id = rm.member_principal_id
					JOIN	sys.database_principals r ON r.principal_id = rm.role_principal_id
					WHERE	r.name = ''db_owner''

					UNION

					SELECT		(CASE WHEN owners.type = ''R'' THEN r.name ELSE owners.name END) name
					FROM		(
								SELECT	p.principal_id,
										p.name,
										p.type,
										p.type_desc
								FROM	sys.database_principals p
								JOIN	sys.database_role_members rm ON p.principal_id = rm.member_principal_id
								JOIN	sys.database_principals r ON r.principal_id = rm.role_principal_id
								WHERE	r.name = ''db_owner''
										AND p.name <> ''dbo''
								) owners
					LEFT JOIN	sys.database_role_members rm ON owners.principal_id = rm.role_principal_id
					LEFT JOIN	sys.database_principals r ON r.principal_id = rm.member_principal_id
					'

	--- Get databases db_owner's
	IF EXISTS ( SELECT TOP 1 * FROM sys.databases WHERE name = @database_name )
		BEGIN
		INSERT INTO @db_owners
			EXEC (@sSQL)
		END
		  
	--- Get the user's memberships in domain groups
	IF EXISTS	(
				SELECT	TOP 1 * 
				FROM	sys.server_principals 
				WHERE	name = @user_name
						AND type IN ( 'U', 'G' )
				)
		OR NOT EXISTS (
				SELECT	TOP 1 * 
				FROM	sys.server_principals 
				WHERE	name = @user_name
				)
		BEGIN
		INSERT INTO @win_users
			exec	xp_logininfo @user_name, 'all'
		END
		  
	--- Is sysadmin?
	IF	EXISTS ( SELECT TOP 1 * FROM @win_users WHERE privilege = 'admin' AND mapped_login_name = @user_name )
			OR IS_SRVROLEMEMBER( 'sysadmin', @user_name ) = 1 
		SELECT	@has_rights = 1, @reason = 'sysadmin'

	--- Is dbcreator (for a new database)?
	ELSE IF NOT EXISTS ( SELECT TOP 1 * FROM sys.databases WHERE name = @database_name )
			AND IS_SRVROLEMEMBER( 'dbcreator', @user_name ) = 1
		SELECT	@has_rights = 1, @reason = 'dbcreator / new database'
		  
	--- Is db_owner (for existing database)?
	ELSE IF EXISTS	(
					SELECT	TOP 1 p.* 
					FROM	@db_owners p
					WHERE	p.name = @user_name
					)
			OR EXISTS (
					SELECT	TOP 1 p.name
					FROM	@db_owners p
					JOIN	@win_users w ON p.name = w.permission_path
							AND w.mapped_login_name = @user_name
					)
		SELECT	@has_rights = 1, @reason = 'db_owner / existing database'
		
	ELSE
		BEGIN
		--- Is dbcreator through a group membership (for a new database)?
		IF NOT EXISTS ( SELECT TOP 1 * FROM sys.databases WHERE name = @database_name )
			BEGIN
			DECLARE crs CURSOR FOR SELECT permission_path FROM @win_users
			OPEN crs
			FETCH NEXT FROM crs INTO @group_name
			WHILE @@FETCH_STATUS <> -1 AND @has_rights = 0
				BEGIN
				SELECT	@has_rights = IS_SRVROLEMEMBER( 'dbcreator', @group_name ),
						@reason = 'dbcreator / new database'
				FETCH NEXT FROM crs INTO @group_name
				END
			CLOSE crs
			DEALLOCATE crs
			END
		END

	--- Return a resultset of the response
	SELECT	@is_allowed = @has_rights
  END
  
SET NOCOUNT OFF
